import pymysql
from pymysql.cursors import DictCursor

# 连接配置 - 替换为你的实际信息
config = {
    'host': 'gateway01.ap-southeast-1.prod.aws.tidbcloud.com',  
    'port': 4000,  # 默认端口
    'user': '3SZFNY6i1wPmRfJ.root',
    'password': '0k9S7jjMh8z9mDaN',
    'database': 'test',
    'ssl_verify_cert': True,  
    'ssl_verify_identity': True,
}

def get_connection():
    """获取数据库连接"""
    return pymysql.connect(
        host=config['host'],
        port=config['port'],
        user=config['user'],
        password=config['password'],
        database=config['database'],
        cursorclass=DictCursor,
        ssl={
            'ca': './isrgrootx1.pem'  
            # Windows 可能需要下载证书
        } if config.get('ssl_verify_cert') else None
    )

def setup_database():
    """初始化数据库和表"""
    with get_connection() as conn:
        with conn.cursor() as cursor:
            # 创建表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS test (
                    id BIGINT PRIMARY KEY AUTO_RANDOM,
                    name VARCHAR(50) NOT NULL,
                    email VARCHAR(100) UNIQUE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)
            conn.commit()
            print("Table 'test' created successfully")

def crud_operations():
    """CRUD 操作示例"""
    with get_connection() as conn:
        # 创建 (Create)
        with conn.cursor() as cursor:
            cursor.execute("""
                INSERT INTO test (name, email) 
                VALUES (%s, %s)
            """, ('Alice', 'alice@example.com'))
            user_id = cursor.lastrowid
            conn.commit()
            print(f"Created user with ID: {user_id}")

        # 读取 (Read)
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM test WHERE id = %s", (user_id,))
            user = cursor.fetchone()
            print("Read user:", user)

        # 更新 (Update)
        with conn.cursor() as cursor:
            cursor.execute("""
                UPDATE test SET email = %s 
                WHERE id = %s
            """, ('alice.new@example.com', user_id))
            conn.commit()
            print(f"Updated user {user_id}")

        # 验证更新
        with conn.cursor() as cursor:
            cursor.execute("SELECT email FROM test WHERE id = %s", (user_id,))
            updated_email = cursor.fetchone()['email']
            print(f"User's new email: {updated_email}")

        # 删除 (Delete)
        with conn.cursor() as cursor:
            cursor.execute("DELETE FROM test WHERE id = %s", (user_id,))
            conn.commit()
            print(f"Deleted user {user_id}")

        # 验证删除
        with conn.cursor() as cursor:
            cursor.execute("SELECT COUNT(*) AS count FROM test WHERE id = %s", (user_id,))
            result = cursor.fetchone()
            print(f"User exists after deletion? {result['count'] > 0}")


def read():
    """<UNK>"""
    with get_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM test")
            user = cursor.fetchone()
            print("Read user:", user)

def insert():
    """向 test 表插入一条记录"""
    with get_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute("INSERT INTO test (name, email) VALUES (%s, %s)", ('elsa', 'elsa@123456.com'))
        conn.commit()  # 提交事务
    print("Insert completed.")

if __name__ == '__main__':
    print("=== TiDB Hello World ===")
    setup_database()
    crud_operations()
    #read()
    #insert()
    print("=== CRUD Operations Completed ===")